-- Tests of data integrity
-- Step 1 Compare the location data in location table with the one in post_universal table
	(SELECT location FROM "location")
EXCEPT
	(SELECT DISTINCT ownerlocation FROM posts_universal WHERE ownerlocation IS NOT NULL)
UNION
	(SELECT DISTINCT ownerlocation FROM posts_universal WHERE ownerlocation IS NOT NULL)
EXCEPT
	(SELECT location FROM "location");


-- Step 2 Compare the author data in author table with the one in post_universal table
	(SELECT author.id, display_name, creation_date, age, "location"."location"
	FROM author
	JOIN "location" ON author.location_id = "location".id)
EXCEPT
	(SELECT DISTINCT ownerid, ownerdisplayname, ownercreationdate, ownerage, ownerlocation
	FROM posts_universal
	WHERE ownerlocation IS NOT NULL)
UNION
	(SELECT DISTINCT ownerid, ownerdisplayname, ownercreationdate, ownerage, ownerlocation
	FROM posts_universal
	WHERE ownerlocation IS NOT NULL)
EXCEPT
	(SELECT author.id, display_name, creation_date, age, "location"."location"
	FROM author
	JOIN "location" ON author.location_id = "location".id);


-- Step 3 Compare the answer table data with the one from posts_universal table.
-- This revealed those answers where the author had a missing location hence added Step 2b)
	(SELECT answer.id, answer.body, answer.score, answer.creation_date, author.display_name, author.creation_date, answer.question_id
	FROM answer
	JOIN author ON author.id = answer.author_id)
EXCEPT
	(SELECT DISTINCT posts_universal.id, body, score, creationdate, ownerdisplayname, ownercreationdate, parentid
	FROM posts_universal
	WHERE posts_universal.posttypeid = 2)
UNION
	(SELECT DISTINCT posts_universal.id, body, score, creationdate, ownerdisplayname, ownercreationdate, parentid
	FROM posts_universal
	WHERE posts_universal.posttypeid = 2)
EXCEPT
	(SELECT answer.id, answer.body, answer.score, answer.creation_date, author.display_name, author.creation_date, answer.question_id
	FROM answer
	JOIN author ON author.id = answer.author_id);

-- Step 4 Compare the question table data with the one from post_universal table.
-- Prerequisite: PERFORM step 0. IN ORDER TO have a clean DATABASE
	(SELECT id, title, body, score, creationdate, closeddate, ownerdisplayname, acceptedanswerid
	FROM posts_universal
	WHERE posttypeid = 1)
EXCEPT
	(SELECT question.id, title, body, score, question.creation_date, closed_date, author.display_name, question.accepted_answer_id
	FROM question
	JOIN author ON question.author_id = author.id)
UNION
	(SELECT question.id, title, body, score, question.creation_date, closed_date, author.display_name, question.accepted_answer_id
	FROM question
	JOIN author ON question.author_id = author.id)
EXCEPT
	(SELECT id, title, body, score, creationdate, closeddate, ownerdisplayname, acceptedanswerid
	FROM posts_universal
	WHERE posttypeid = 1);
	
-- Step 5 Compare the linked_question data with the one from posts_universal table.
	(SELECT question_id, linked_question_id
	FROM linked_question)
EXCEPT
	(SELECT id, linkpostid FROM posts_universal WHERE linkpostid IS NOT NULL)
UNION
    (SELECT id, linkpostid FROM posts_universal WHERE linkpostid IS NOT NULL)
EXCEPT
	(SELECT question_id, linked_question_id
	FROM linked_question);
	
-- Step 6+7 Compare the tag and question_tag DATA WITH the NONE FROM posts_universal TABLE.
-- ISSUE c and r tags are added to innocent questions due to the LIKE condition on migration step 7
SELECT tags FROM posts_universal WHERE id = 14352483;
SELECT tag
FROM tag
JOIN question_tag ON tag.id = question_tag.tag_id
JOIN question  ON question.id = question_tag.question_id
WHERE question.id = 14352483;


-- CHECK IF we have the same number OF posts AS there were INITIALLY:
SELECT count(DISTINCT id) FROM posts_universal; -- 13380
SELECT count(*) FROM answer; -- 11182
SELECT count(*) FROM question; -- 2198

-- Check if we have the same number of comments as there were initially,
SELECT count(DISTINCT commentid) FROM comments_universal; -- 31426
SELECT count(id) FROM "comment"; -- 31426



-- testing functions and procedures

-- sign up a user 
SELECT sign_up('Troels', 'troels@ruc.bug', 'troels123');

-- search -- very slow.
SELECT * FROM search('element', 1);

-- mark question and then update the question with annotation
SELECT mark_question(1, 13724063);
SELECT mark_question(1, 13724063, 'This is an amazing thing to know');

SELECT * FROM marked_question;